import pandas as pd
import re

from app.models import db
from app.models.collection import ParkingCollection, DataCollection
from app.models.ruel_cost import RuelCost
from app.models.road_toll import RoadToll
from app.models.parking_rate import ParkingRate
from app.models.car import CarInfo


class ImportFileTools(object):
    def __init__(self):
        pass

    def ruel(self, file):
        df = pd.read_excel(file)

        for data_line in range(df.shape[0]):

            if df.iloc[data_line][1] != "" and df.iloc[data_line][1] != "样车编号":
                car = CarInfo.query.filter(
                    CarInfo.car_num.contains(df.iloc[data_line][1].split("-")[1])
                ).first()
                if car:
                    ruel = RuelCost(
                        car=car.id,
                        create_time=df.iloc[data_line][2],
                        mileage=df.iloc[data_line][3],
                        oil_quantity=df.iloc[data_line][4],
                        price=df.iloc[data_line][5],
                        total_price=df.iloc[data_line][6],
                        engineer=df.iloc[data_line][7],
                    )

                    db.session.add(ruel)
                    db.session.commit()

    def road(self, file):

        df = pd.read_excel(file)

        for data_line in range(df.shape[0]):

            if df.iloc[data_line][1] not in ["过路费", "样车编号"]:
                car = CarInfo.query.filter(
                    CarInfo.car_num.contains(df.iloc[data_line][1].split("-")[1])
                ).first()
                if car.id:
                    road = RoadToll(
                        car=car.id,
                        create_time=df.iloc[data_line][2],
                        enterance=df.iloc[data_line][3],
                        export=df.iloc[data_line][4],
                        price=df.iloc[data_line][5],
                        engineer=df.iloc[data_line][6],
                    )

                    db.session.add(road)
                    db.session.commit()

    def parking(self, file):
        df = pd.read_excel(file)

        for data_line in range(df.shape[0]):

            if df.iloc[data_line][1] != "" and df.iloc[data_line][1] != "样车编号":
                car = CarInfo.query.filter(
                    CarInfo.car_num.contains(df.iloc[data_line][1].split("-")[1])
                ).first()
                if car:
                    park = ParkingRate(
                        car=car.id,
                        create_time=df.iloc[data_line][2],
                        parking=df.iloc[data_line][3],
                        price=df.iloc[data_line][7],
                        engineer=df.iloc[data_line][8],
                    )

                    db.session.add(park)
                    db.session.commit()
        pass

    def collection(self, file):

        df = pd.read_excel(file)
        df.fillna(0, inplace=True)  # 将空值填为0

        for data_line in range(df.shape[0]):
            if df.iloc[data_line]["日期"] == 0:
                continue
            car = CarInfo.query.filter(
                CarInfo.car_num.contains(str(df.iloc[data_line]["工程名"])[0:3])
            ).first()
            if car:
                collection = DataCollection(
                    car=car.id,
                    create_time=str(df.iloc[data_line]["日期"]),
                    area=df.iloc[data_line]["省份"],
                    city=df.iloc[data_line]["城市"],
                    project_name=df.iloc[data_line]["工程名"],
                    # update_time=df.iloc[data_line]["上传百度时间"],
                    course=float(df.iloc[data_line]["实际采集里程（KM）"])
                    if type(df.iloc[data_line]["实际采集里程（KM）"]) != "nan"
                    else 0,
                    file_size=float(df.iloc[data_line]["文件大小（GB）"])
                    if type(df.iloc[data_line]["文件大小（GB）"]) != "nan"
                    else 0,
                    file_num=float(df.iloc[data_line]["文件数量"])
                    if type(df.iloc[data_line]["文件数量"]) != "nan"
                    else 0,
                    work_time=float(df.iloc[data_line]["作业时长"])
                    if type(df.iloc[data_line]["作业时长"]) != "nan"
                    else 0,
                    road_category=df.iloc[data_line]["道路类别"],
                    collection_place=df.iloc[data_line]["采集地点"],
                    follow_user=df.iloc[data_line]["跟车人员"],
                )

                db.session.add(collection)
                db.session.commit()

    def parking_collection(self, file):
        pass
